import os
import sys
from PyQt5.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout, 
                            QHBoxLayout, QLabel, QPushButton, QLineEdit, 
                            QFileDialog, QMessageBox, QFrame)
from PyQt5.QtGui import QIntValidator, QFont
from PyQt5.QtCore import Qt
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from PIL import Image as PILImage  # 新增Pillow库用于图像处理
import io

class ExcelImageInserter(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Excel批量插图工具")
        self.setFixedSize(500, 450)  # 稍微增加宽度以适应现代化UI
        
        # 设置现代化样式
        self.setStyleSheet("""
            QMainWindow {
                background-color: #f5f5f5;
            }
            QLabel {
                color: #555;
                font-size: 14px;
            }
            QPushButton {
                background-color: #4CAF50;
                color: white;
                border: none;
                padding: 8px 16px;
                border-radius: 4px;
                font-size: 14px;
                min-width: 100px;
            }
            QPushButton:hover {
                background-color: #45a049;
            }
            QPushButton:pressed {
                background-color: #3d8b40;
            }
            QLineEdit {
                border: 1px solid #ddd;
                border-radius: 4px;
                padding: 6px;
                font-size: 14px;
            }
            QFrame {
                background-color: white;
                border-radius: 8px;
            }
            #title {
                font-size: 18px;
                font-weight: bold;
                color: #333;
            }
            #section {
                font-size: 16px;
                color: #444;
                font-weight: 500;
            }
        """)
        
        # 初始化UI
        self.init_ui()
        
        # 存储文件路径
        self.excel_path = ""
        self.image_folder = ""
    
    def init_ui(self):
        # 中央部件
        central_widget = QWidget()
        self.setCentralWidget(central_widget)
        
        # 主布局
        main_layout = QVBoxLayout(central_widget)
        main_layout.setContentsMargins(20, 20, 20, 20)
        main_layout.setSpacing(15)
        
        # 标题
        title_label = QLabel("Excel批量插图工具")
        title_label.setObjectName("title")
        title_label.setAlignment(Qt.AlignCenter)
        main_layout.addWidget(title_label)
        
        # 添加分隔线
        separator = QFrame()
        separator.setFrameShape(QFrame.HLine)
        separator.setFrameShadow(QFrame.Sunken)
        main_layout.addWidget(separator)
        
        # 文件选择区域
        file_frame = QFrame()
        file_frame.setObjectName("frame")
        file_layout = QVBoxLayout(file_frame)
        file_layout.setContentsMargins(15, 15, 15, 15)
        file_layout.setSpacing(10)
        
        file_section = QLabel("文件选择")
        file_section.setObjectName("section")
        file_layout.addWidget(file_section)
        
        # Excel文件选择
        excel_layout = QHBoxLayout()
        self.excel_label = QLabel("Excel文件:")
        self.excel_path_label = QLabel("未选择文件")
        self.excel_path_label.setStyleSheet("color: #888; font-style: italic;")
        self.excel_btn = QPushButton("选择文件")
        self.excel_btn.clicked.connect(self.select_excel)
        
        excel_layout.addWidget(self.excel_label)
        excel_layout.addWidget(self.excel_path_label)
        excel_layout.addStretch()
        excel_layout.addWidget(self.excel_btn)
        file_layout.addLayout(excel_layout)
        
        # 图片文件夹选择
        image_layout = QHBoxLayout()
        self.image_label = QLabel("图片文件夹:")
        self.image_path_label = QLabel("未选择文件夹")
        self.image_path_label.setStyleSheet("color: #888; font-style: italic;")
        self.image_btn = QPushButton("选择文件夹")
        self.image_btn.clicked.connect(self.select_image_folder)
        
        image_layout.addWidget(self.image_label)
        image_layout.addWidget(self.image_path_label)
        image_layout.addStretch()
        image_layout.addWidget(self.image_btn)
        file_layout.addLayout(image_layout)
        
        main_layout.addWidget(file_frame)
        
        # 设置区域
        settings_frame = QFrame()
        settings_frame.setObjectName("frame")
        settings_layout = QVBoxLayout(settings_frame)
        settings_layout.setContentsMargins(15, 15, 15, 15)
        settings_layout.setSpacing(10)
        
        settings_section = QLabel("插入设置")
        settings_section.setObjectName("section")
        settings_layout.addWidget(settings_section)
        
        # 图片尺寸设置
        size_layout = QHBoxLayout()
        size_layout.addWidget(QLabel("图片尺寸:"))
        
        self.width_label = QLabel("宽(px):")
        self.width_input = QLineEdit("100")
        self.width_input.setValidator(QIntValidator(10, 1000))
        
        self.height_label = QLabel("高(px):")
        self.height_input = QLineEdit("100")
        self.height_input.setValidator(QIntValidator(10, 1000))
        
        size_layout.addWidget(self.width_label)
        size_layout.addWidget(self.width_input)
        size_layout.addWidget(self.height_label)
        size_layout.addWidget(self.height_input)
        settings_layout.addLayout(size_layout)
        
        # 列设置
        col_layout = QHBoxLayout()
        col_layout.addWidget(QLabel("列设置:"))
        
        self.ref_label = QLabel("图片引用列:")
        self.ref_input = QLineEdit("A")
        self.ref_input.setMaxLength(1)
        
        self.insert_col_label = QLabel("插入列:")
        self.insert_col_input = QLineEdit("B")
        self.insert_col_input.setMaxLength(1)
        
        col_layout.addWidget(self.ref_label)
        col_layout.addWidget(self.ref_input)
        col_layout.addWidget(self.insert_col_label)
        col_layout.addWidget(self.insert_col_input)
        settings_layout.addLayout(col_layout)
        
        main_layout.addWidget(settings_frame)
        
        # 执行按钮
        self.execute_btn = QPushButton("开始插入图片")
        self.execute_btn.clicked.connect(self.insert_images)
        self.execute_btn.setStyleSheet("""
            QPushButton {
                background-color: #2196F3;
                font-size: 16px;
                padding: 10px;
                min-width: 150px;
            }
            QPushButton:hover {
                background-color: #0b7dda;
            }
        """)
        
        btn_container = QWidget()
        btn_layout = QHBoxLayout(btn_container)
        btn_layout.addStretch()
        btn_layout.addWidget(self.execute_btn)
        btn_layout.addStretch()
        
        main_layout.addWidget(btn_container)
        main_layout.addStretch()
    
    def select_excel(self):
        file_path, _ = QFileDialog.getOpenFileName(
            self, "选择Excel文件", "", "Excel文件 (*.xlsx *.xls)"
        )
        if file_path:
            self.excel_path = file_path
            self.excel_path_label.setText(os.path.basename(file_path))
            self.excel_path_label.setStyleSheet("color: #333; font-style: normal;")
    
    def select_image_folder(self):
        folder_path = QFileDialog.getExistingDirectory(self, "选择图片文件夹")
        if folder_path:
            self.image_folder = folder_path
            self.image_path_label.setText(os.path.basename(folder_path))
            self.image_path_label.setStyleSheet("color: #333; font-style: normal;")
    
    def insert_images(self):
        if not self.excel_path:
            self.show_error_message("请先选择Excel文件!")
            return
        
        if not self.image_folder:
            self.show_error_message("请先选择图片文件夹!")
            return
        
        try:
            width = int(self.width_input.text())
            height = int(self.height_input.text())
            ref_col = self.ref_input.text().upper()
            insert_col = self.insert_col_input.text().upper()
            
            if not ref_col.isalpha() or not insert_col.isalpha():
                self.show_error_message("请输入有效的列字母(A-Z)!")
                return
            
            # 加载Excel文件
            wb = load_workbook(self.excel_path)
            ws = wb.active
            
            # 获取图片文件夹中的所有图片
            image_files = []
            for file in os.listdir(self.image_folder):
                if file.lower().endswith(('.png', '.jpg', '.jpeg', '.gif', '.bmp')):
                    image_files.append(file)
            
            if not image_files:
                self.show_error_message("图片文件夹中没有找到支持的图片格式!")
                return
            
            # 处理每一行
            inserted_count = 0
            for row in range(1, ws.max_row + 1):
                cell_value = ws[f"{ref_col}{row}"].value
                if not cell_value:
                    continue
                
                # 查找匹配的图片
                found_image = None
                for img_file in image_files:
                    img_name = os.path.splitext(img_file)[0]
                    if str(cell_value).strip() == img_name.strip():
                        found_image = img_file
                        break
                
                if found_image:
                    try:
                        img_path = os.path.join(self.image_folder, found_image)
                        
                        # 使用Pillow处理图像以确保兼容性
                        pil_img = PILImage.open(img_path)
                        
                        # 调整图像大小
                        pil_img = pil_img.resize((width, height), PILImage.LANCZOS)
                        
                        # 将图像保存到字节流
                        img_byte_arr = io.BytesIO()
                        if img_path.lower().endswith('.png'):
                            pil_img.save(img_byte_arr, format='PNG')
                        else:
                            pil_img.save(img_byte_arr, format='JPEG')
                        
                        # 创建openpyxl图像对象
                        img_byte_arr.seek(0)
                        img = Image(img_byte_arr)
                        
                        # 设置图片尺寸
                        img.width = width
                        img.height = height
                        
                        # 插入到指定列
                        ws.add_image(img, f"{insert_col}{row}")
                        
                        # 调整行高以适应图片
                        row_height = (height * 0.75) * 1.1  # 像素转磅，并增加10%空间
                        ws.row_dimensions[row].height = row_height
                        
                        inserted_count += 1
                    except Exception as e:
                        print(f"插入图片 {found_image} 失败: {str(e)}")
                        continue
            
            # 调整图片列的列宽
            col_width = (width * 0.14) + 2  # 经验公式
            ws.column_dimensions[insert_col].width = col_width
            
            # 保存Excel文件
            save_path = os.path.splitext(self.excel_path)[0] + "_with_images.xlsx"
            wb.save(save_path)
            
            self.show_success_message(
                f"成功插入 {inserted_count} 张图片!\n文件已保存为:\n{save_path}"
            )
            
        except Exception as e:
            self.show_error_message(f"处理过程中发生错误:\n{str(e)}")
    
    def show_error_message(self, message):
        msg = QMessageBox(self)
        msg.setIcon(QMessageBox.Critical)
        msg.setWindowTitle("错误")
        msg.setText(message)
        msg.setStyleSheet("""
            QMessageBox {
                background-color: white;
            }
            QLabel {
                color: #333;
                font-size: 14px;
            }
        """)
        msg.exec_()
    
    def show_success_message(self, message):
        msg = QMessageBox(self)
        msg.setIcon(QMessageBox.Information)
        msg.setWindowTitle("完成")
        msg.setText(message)
        msg.setStyleSheet("""
            QMessageBox {
                background-color: white;
            }
            QLabel {
                color: #333;
                font-size: 14px;
            }
        """)
        msg.exec_()

if __name__ == "__main__":
    app = QApplication(sys.argv)
    
    # 设置全局字体
    font = QFont("Microsoft YaHei", 10)
    app.setFont(font)
    
    window = ExcelImageInserter()
    window.show()
    sys.exit(app.exec_())